package com.liuyang.demo.excel.util;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Description:导出
 */
public class ExcelExportUtil {

    /**
     * 创建excel文档，
     *
     * @param list        数据
     * @param keys        list中map的key数组集合
     * @param columnNames excel的列名
     */
    public static XSSFWorkbook createWorkBook(List<Map<String, Object>> list, String[] keys, String[] columnNames) {
        // 创建excel工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        return createWorkBook(wb, list, keys, columnNames, "sheet1");
    }

    /**
     * 多个sheet表格
     *
     * @param wb
     * @param list
     * @param keys
     * @param columnNames
     * @param sheetName   当前sheet的名称
     * @return
     */
    public static XSSFWorkbook createWorkBook(XSSFWorkbook wb, List<Map<String, Object>> list, String[] keys, String[] columnNames, String sheetName) {
        return createWorkBook(wb, list, keys, columnNames, sheetName, new ArrayList<>());
    }

    /**
     * 追加数据
     *
     * @param wb
     * @param list
     * @param keys
     * @param columnNames
     * @param sheetName
     * @param sheet
     * @return
     */
    public static XSSFWorkbook createWorkBook(XSSFWorkbook wb, List<Map<String, Object>> list, String[] keys, String[] columnNames, String sheetName, Sheet sheet) {
        return createWorkBook(wb, list, keys, columnNames, sheetName, null,sheet);
    }

    public static XSSFWorkbook createWorkBook(XSSFWorkbook wb, List<Map<String, Object>> list, String[] keys, String[] columnNames, String sheetName, List<CellRangeAddress> ranges) {
        return createWorkBook(wb,  list, keys, columnNames, sheetName, ranges, null);
    }

    public static XSSFWorkbook createWorkBook(XSSFWorkbook wb, List<Map<String, Object>> list, String[] keys, String[] columnNames, String sheetName, List<CellRangeAddress> ranges, Sheet sheet) {
        // 创建excel工作簿
        if (sheet == null) {
            sheet = wb.createSheet(sheetName);
        }
        Integer lastRowNum = sheet.getLastRowNum();
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        if (ranges != null) {
            for (CellRangeAddress range : ranges) {
                sheet.addMergedRegion(range);
            }
        }
        for (int i = 0; i < columnNames.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 设置表头
        setCellTitle(columnNames, wb, sheet);

        // 创建第一行
        CellStyle cs2 = getCellStyle(wb);
        if (list != null && list.size() > 0) {
            int size = list.size();
            int keyLen = keys.length;
            //设置每行每列的值
            for (int i = 0 + lastRowNum; i < size + lastRowNum; i++) {
                // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
                // 创建一行，在页sheet上
                Row row1 = sheet.createRow(i + 1);
                // 在row行上创建一个方格
                for (short j = 0; j < keyLen; j++) {
                    Cell cell = row1.createCell(j);
                    setCellValue(cell, list.get(i-lastRowNum).get(keys[j]));
                    cell.setCellStyle(cs2);
                }
            }
        }

        return wb;
    }

    private static void setCellValue(Cell cell, Object obj) {
        if (obj != null && obj instanceof BigDecimal) {
            cell.setCellValue(Double.valueOf(obj.toString()));
        } else if (obj != null && obj instanceof Double) {
            cell.setCellValue(Double.valueOf(obj.toString()));
        } else if (obj != null && obj instanceof String) {
            cell.setCellValue(obj.toString());
        } else if (obj != null && (obj instanceof Integer || isNumber(obj.toString()))) {
            cell.setCellValue(Integer.valueOf(obj.toString()));
        } else if (obj != null && (obj instanceof Date)) {
            cell.setCellValue(obj.toString());
        } else {
            cell.setCellValue(obj != null ? obj.toString() : "");
        }
    }

    private static void setStyle(CellStyle cs, Font f) {
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_LEFT);
    }

    public static boolean isNumber(String str) {
        java.util.regex.Pattern pattern = java.util.regex.Pattern.compile("[1-9]{1}\\d{0,9}");
        java.util.regex.Matcher match = pattern.matcher(str);
        if (match.matches()) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 创建excel文档，
     *
     * @param dataList    数据
     * @param columnNames excel的列名
     * @param sheetName
     */
    public static XSSFWorkbook createWorkBook(List<Object[]> dataList, String[] columnNames, String sheetName) {
        // 创建excel工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(sheetName);
        // 设置表头
        setCellTitle(columnNames, wb, sheet);

        // 数据填充
        if (CollectionUtils.isNotEmpty(dataList)) {
            // 获取样式
            CellStyle cs = getCellStyle(wb);
            //设置每行每列的值
            for (int i = 0; i < dataList.size(); i++) {
                // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
                // 创建一行，在页sheet上
                Row row1 = sheet.createRow(i + 1);
                // 在row行上创建一个方格
                Object[] objectValues = dataList.get(i);
                for (int j = 0; j < objectValues.length; j++) {
                    Cell cell = row1.createCell(j);
                    setCellValue(cell, objectValues[j]);
                    cell.setCellStyle(cs);
                }
            }
        }

        return wb;
    }

    /**
     * 创建excel文档，
     *
     * @param dataList    数据
     * @param columnNames excel的列名
     */
    public static XSSFWorkbook createWorkBook(List<Object[]> dataList, String[] columnNames) {
        return createWorkBook(dataList, columnNames, "团购申请");
    }

    private static void setCellTitle(String[] columnNames, XSSFWorkbook wb, Sheet sheet) {
        if (columnNames == null || columnNames.length == 0) {
            return;
        }

        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < columnNames.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow((short) 0);
        CellStyle cs = wb.createCellStyle();

        // 创建字体
        Font f = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        setStyle(cs, f);

        //设置列名
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
    }

    private static CellStyle getCellStyle(XSSFWorkbook wb) {

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        // 创建第二种字体样式（用于值）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());

        // 设置第二种单元格的样式（用于值）
        setStyle(cs, f);

        return cs;
    }

}








   